﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace Framework.Db
{
    /// <summary>
    /// Mysql数据库操作类
    /// </summary>
    public class MysqlHelper : IDataProvider
    {
        string connStr = null;

        MySqlConnection mycn = new MySqlConnection();
        MySqlCommand mycm = new MySqlCommand();

        /// <summary>
        /// 构造函数,初始化数据库连接
        /// </summary>
        public MysqlHelper()
        {
            if (mycn.State == System.Data.ConnectionState.Closed)
            {
                initConnStr();
                try
                {
                    mycn.Open();
                    System.Console.WriteLine("open oK");
                }
                catch (MySqlException ex)
                {
                    System.Console.WriteLine("not open DB, Exception:" + ex);
                }
            }
        }

        /// <summary>
        /// 获取数据库连接参数信息
        /// </summary>
        private void initConnStr()
        {
            connStr = string.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false;charset={4}", Kit.AppOpr.GetConfigStrings("db_host"), Kit.AppOpr.GetConfigStrings("db_user"), Kit.AppOpr.GetConfigStrings("db_pwd"), Kit.AppOpr.GetConfigStrings("db_name"), Kit.AppOpr.GetConfigStrings("db_charset"));
            mycn.ConnectionString = connStr;
        }

        /// <summary>
        /// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1   
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>影响行数,否则-1</returns>
        public int ExecuteNonQuery(string sql)
        {
            int i = -1;
            try
            {
                if (sql == null || sql == string.Empty)
                {
                    return i;
                }
                else
                {
                    mycm = new MySqlCommand(sql, mycn);
                    i = mycm.ExecuteNonQuery();
                }
               
            }
            catch (MySqlException ex)
            {
                System.Console.WriteLine(ex);
            }
            return i;
        }

        /// <summary>
        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
        /// </summary>
        /// <param name="sql">SELECT 语句</param>
        /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>
        public object ExecuteScalar(string sql)
        {
            object i = null;
            try
            {
                if (sql == null || sql == string.Empty)
                {
                    return i;
                }
                else
                {
                    mycm = new MySqlCommand(sql, mycn);
                    i = mycm.ExecuteScalar();
                }
            }
            catch (MySqlException ex)
            {
                System.Console.WriteLine(ex);
            }
            return i;
        }

        /// <summary>
        /// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回 
        /// </summary>
        /// <param name="sql">SELECT 语句</param>
        /// <returns>数据集 DataSet</returns>
        public DataSet RetriveDataSet(string sql)
        {
            if (sql == null || sql == string.Empty)
            {
                return null;
            }
            using (MySqlDataAdapter da = new MySqlDataAdapter(sql, mycn))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds);
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.ToString());
                }
                return ds;
            }
        }

        public DataSet RetriveDataSet(string[] sql, params string[] tableName)
        {
            System.Data.DataSet ds = null;
            return ds;
        }

        public System.Data.DataSet UpdateDataSet(string sql, System.Data.DataSet hasChangesDataSet)
        {
            System.Data.DataSet ds = null;
            return ds;
        }

        /// <summary>
        /// 关闭数据库的连接
        /// </summary>
        public void Dispose()
        {
            //mycn.Close();
        }
    }
}
